import pymysql
import math


# 动态获取表的总行数
def get_table_row_count(db):
    cursor = db.cursor()
    cursor.execute("SELECT COUNT(*) FROM tb_job")
    row_count = cursor.fetchone()[0]
    return row_count


def hoop(start, end, interval):
    # mysql 数据库连接
    db = pymysql.connect(host='127.0.0.1', user='root', password='123456', port=3306, database='flask_job',
                         charset='utf8')

    # 获取操作游标
    cursor = db.cursor()

    # 获取数据并处理
    sql = "SELECT * FROM tb_job LIMIT %s, %s" % (start, interval)
    cursor.execute(sql)
    jobs = cursor.fetchall()

    for job in jobs:
        washdata(job, db)

    db.close()


def transformMoney(s):
    if s[-1] == '千':
        money0 = 1000
    elif s[-1] == '万':
        money0 = 10000
    else:
        money0 = 1
    return money0


def washdata(item, db):
    try:
        s = item[5].split('-')
        size = item[12].split('-')
        time = item[13].split('-')

        if len(s) > 1:
            salary0 = float(s[0][:-1]) * transformMoney(s[0])
            salary1 = float(s[1][:-1]) * transformMoney(s[1]) if s[1][-1] != '天' else float(s[1][:-2]) * transformMoney(
                s[1])
        else:
            salary0 = 0
            salary1 = 1

        if len(size) > 1:
            cosize0 = float(size[0])
            cosize1 = float(size[1][:-1])
        else:
            cosize0 = cosize1 = 0

        if len(time) > 1:
            worktime0 = float(time[0])
            worktime1 = float(time[1][:-1])
        else:
            worktime0 = worktime1 = 0

        cursor = db.cursor()
        sql = """INSERT INTO tb_job2(id, number, company_name, position_name, city, salary0, salary1, degree,
                                     company_logo, url, company_url, education, coattr, cosize0, cosize1, worktime0, worktime1, welfare, publish_time)
                  VALUES (%d, '%s',  '%s', '%s','%s', %f, %f,  '%s',  '%s',  '%s','%s',  '%s',  '%s',  %f,  %f,  %f,  %f,  '%s',  '%s')""" % \
              (item[0], item[1], item[2], item[3], item[4], salary0, salary1, item[6], item[7], item[8],
               item[9], item[10], item[11], cosize0, cosize1, worktime0, worktime1, item[14], item[15])

        try:
            cursor.execute(sql)
            db.commit()
        except Exception as e:
            db.rollback()  # 发生错误时回滚
            print(e)
    except:
        pass


def main():
    # 连接到数据库
    db = pymysql.connect(host='127.0.0.1', user='root', password='123456', port=3306, database='flask_job',
                         charset='utf8')

    # 获取表的总行数
    row_count = get_table_row_count(db)

    # 定义每次查询的间隔, 这里根据数据量调整, 可以设置为数据库表总行数的1/10
    interval = math.ceil(row_count / 120)  # 假设分成120批次
    print(f"每次处理的条目数：{interval}")

    db.close()

    start = 0
    total = row_count

    # 按照分页处理数据
    while start < total:
        hoop(start, interval, interval)
        start += interval
        print(f"处理完成: {start} / {total}")


if __name__ == '__main__':
    main()
